package com.springapp.dao.dao.impl;




import com.springapp.model.Pal_ArtFou;

import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * Created by nicol_000 on 27/08/14.
 */
@Repository
public class PalArtFouDAO  {

    @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
    @Autowired
    private DataSource dataSource;


    public Pal_ArtFou getPal_ArtFou(int _iIdPal_ArtFou){
        String sql="" +
                "SELECT *\n" +
                "FROM Pal_ArtFou\n" +
                "WHERE IDPal_ArtFou='"+_iIdPal_ArtFou+"'";
        Connection conn = null;
        Pal_ArtFou palArtFou=new Pal_ArtFou();
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet res = ps.executeQuery();
            if(res.next()){
                palArtFou.seteIDPalArtFou(res.getInt("IDPal_ArtFou"));
                palArtFou.seteIDPalette(res.getInt("IDPalette"));
                palArtFou.seteIDArtFou(res.getInt("IDArtFou"));
                palArtFou.seteQteArticle(res.getInt("QteArticle"));
                palArtFou.seteQteCarton(res.getInt("QteCarton"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return palArtFou;
    }

    public Pal_ArtFou getPal_ArtFouFromPal(int _iIdPalette){
        String sql="" +
                "SELECT *\n" +
                "FROM Pal_ArtFou\n" +
                "WHERE IDPalette='"+_iIdPalette+"'";
        Connection conn = null;
        Pal_ArtFou palArtFou=new Pal_ArtFou();
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet res = ps.executeQuery();
            if(res.next()){
                palArtFou.seteIDPalArtFou(res.getInt("IDPalArtFou"));
                palArtFou.seteIDPalette(res.getInt("IDPalette"));
                palArtFou.seteIDArtFou(res.getInt("IDArtFou"));
                palArtFou.seteQteArticle(res.getInt("QteArticle"));
                palArtFou.seteQteCarton(res.getInt("QteCarton"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return palArtFou;
    }


    public JSONObject getQteArtFou(int _iIDPalette){
        String sql="" +
                "SELECT QteArticle, IDArtFou FROM Pal_ArtFou WHERE IDPalette='"+_iIDPalette+"'";
        System.out.println(sql);
        Connection conn = null;
        JSONObject jsoRet= new JSONObject();
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs =ps.executeQuery();
            if(rs.next()){
                try {
                    jsoRet.put("QteArticle", rs.getInt("qteArticle"));
                    jsoRet.put("IdArtFou",rs.getInt("IDArtFou"));
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return jsoRet;
    }

    public String update(Pal_ArtFou palArtFou){
        String sRet = "";
        String sql="" +
                "UPDATE Pal_ArtFou SET IDArtFou='"+palArtFou.geteIDArtFou()+"'\n," +
                "QteArticle='"+palArtFou.geteQteArticle()+"'\n," +
                "QteCarton='"+palArtFou.geteQteCarton()+"'\n" +
                "WHERE IDPalette='"+palArtFou.geteIDPalette()+"'";
        System.out.println(sql);
        Connection conn = null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            int iRet=ps.executeUpdate();
            ps.close();
            if(iRet==0){
                sRet="Echec de la modification de la palette";
            }else{
                sRet="PaletteMise à jour";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sRet;

    }
}
